package com.iamteer.day18;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import org.junit.Test;

public class App {

	//连接参数
//	private String url = "jdbc:mysql://localhost:3306/day18";
	private String url = "jdbc:mysql:///day18";
	private String user = "root";
	private String password = "123456";
	
	private Connection con;
	private Statement stmt;
	private PreparedStatement pstmt;
	private CallableStatement cstmt;
	private ResultSet rs;
	
	//1 没有使用防止 sql 注入的案例
	@Test
	public void testLogin1() {
		String username = "tom";
		//String password = "888";
		String password = " ' or 1=1 -- ";	//SQL 注入
		
		//SQL 语句
		String sql = "select * from admin where username='"+username+"' and password='" + password + "' ";
		
		try {
			// 1 加载驱动，获取连接
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(this.url, this.user, this.password);
			
			// 2 创建 stmt对象
			stmt = con.createStatement();
			
			// 3 执行查询
			rs = stmt.executeQuery(sql);
			
			//
			if (rs.next()) {
				System.out.println("登录成功！");
			} else {
				System.out.println("登录失败！");
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			// 4 关闭
			try {
				rs.close();
				stmt.close();
				con.close();
			} catch (Exception e) {
				throw new RuntimeException(e);
			}
		}
	}
	
	//2 防止 sql 注入的案例
	@Test
	public void testLogin2() {
		String username = "tom";
//		String password = "888";
		String password = " ' or 1=1 -- ";	//SQL 注入，不成功！
		
		//SQL 语句
		String sql = "select * from admin where username=? and password=? ";
		
		try {
			// 1 加载驱动，获取连接
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(this.url, this.user, this.password);
			
			// 2 创建 stmt对象
			pstmt = con.prepareStatement(sql);
			//设置占位符值
			pstmt.setString(1, username);
			pstmt.setString(2, password);
			
			// 3 执行查询
			rs = pstmt.executeQuery();
			//
			if (rs.next()) {
				System.out.println("登录成功！");
			} else {
				System.out.println("登录失败！");
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			// 4 关闭
			try {
				rs.close();
				pstmt.close();
				con.close();
			} catch (Exception e) {
				throw new RuntimeException(e);
			}
		}		
	}
	//3 程序调用存储过程
	@Test
	public void testCall() {
		try {
			//1 加载驱动，获取连接
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(this.url, this.user, this.password);
			
			//2 创建执行存储过程的 Statement 对象
			cstmt = con.prepareCall("CALL proc_login");
			//3 执行（存储过程）
			rs = cstmt.executeQuery();
			//4 
			if (rs.next()) {
				String username = rs.getString("username");
				String password = rs.getString("password");
				System.out.println(username + " " + password);
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		
	}
}
